package com.sinbader.xiaole.common.utils;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * excel操作
 * 
 * @author daiyue
 * @version 1.0 2016年12月1日
 * @see com.sinbader.xiaole.common.utils.ExcelUtil
 * @since 1.0
 */
public class ExcelUtil {

    /**
     * 去。0的格式化
     */
    public static final DecimalFormat point = new DecimalFormat("###################.###########");

    /**
     * excel2003格式
     */
    public static final String OFFICE_EXCEL_2003_POSTFIX = ".xls";

    /**
     * excel2007以上格式
     */
    public static final String OFFICE_EXCEL_2007_POSTFIX = ".xlsx";

    /**
     * Excel文件到List
     * 
     * @param file 文件
     * @param sheetIndex 工作表索引(下标从0开始)
     * @return 读取结果列表
     * @throws Exception 异常
     */
    public static List<List<String>> readToList(File file, int sheetIndex)
        throws Exception {
        List<List<String>> ls = new ArrayList<List<String>>();
        Workbook wb = loadWorkbook(null, file, null);
        if (null != wb) {
            Sheet sh = wb.getSheetAt(sheetIndex);
            int rows = sh.getPhysicalNumberOfRows();
            int cells = sh.getRow(0).getPhysicalNumberOfCells();
            for (int i = 0; i < rows; i++ ) {
                Row row = sh.getRow(i);
                if (row == null) {
                    break;
                }
                List<String> r = new ArrayList<String>(cells);
                boolean dataFlag = true;
                for (int c = 0; c < cells; c++ ) {
                    if (row.getCell(c) == null
                        || row.getCell(c).getCellTypeEnum() == CellType.BLANK) {
                        r.add("");
                    }
                    else {
                        dataFlag = false;
                        r.add(getStringCellValue(row.getCell(c)));
                    }
                }
                // 遇到一行全为空结束
                if (dataFlag) {
                    break;
                }
                ls.add(r);
            }
        }
        return ls;
    }

    /**
     * 一次读取Excel多个工作表文件到List
     * 
     * @param fileName 文件名
     * @param file 文件
     * @param inputStream 文件流
     * @param sheetIndexs 工作表索引(下标从0开始)
     * @param skipRows 跳过的表头(行数)
     * @param titleRow 表头序号(下标从0开始)
     * @return 读取结果列表
     * @throws Exception 异常
     */
    public static List<List<List<String>>> readBooksToList(String fileName, File file,
                                                           InputStream inputStream,
                                                           int[] sheetIndexs, int skipRows,
                                                           int titleRow)
        throws Exception {
        Workbook wb = loadWorkbook(fileName, file, inputStream);
        List<List<List<String>>> result = new ArrayList<List<List<String>>>();
        if (null != wb && sheetIndexs != null && sheetIndexs.length > 0) {
            for (int sheetIndex : sheetIndexs) {
                List<List<String>> ls = readToList(sheetIndex, skipRows, titleRow, wb);
                result.add(ls);
            }
        }
        return result;
    }

    /**
     * Excel文件到List
     * 
     * @param fileName 文件名
     * @param file 文件
     * @param inputStream 文件流
     * @param sheetIndex 工作表索引(下标从0开始)
     * @param skipRows 跳过的表头(行数)
     * @param titleRow 表头序号(下标从0开始)
     * @return 读取结果列表
     * @throws Exception 异常
     */
    public static List<List<String>> readToList(String fileName, File file,
                                                InputStream inputStream, int sheetIndex,
                                                int skipRows, int titleRow)
        throws Exception {
        Workbook wb = loadWorkbook(fileName, file, inputStream);
        List<List<String>> ls = new ArrayList<List<String>>();
        if (null != wb) {
            ls = readToList(sheetIndex, skipRows, titleRow, wb);
        }
        return ls;
    }

    /**
     * 读取数据
     * 
     * @param sheetIndex 工作表索引(下标从0开始)
     * @param skipRows 跳过的表头(行数)
     * @param titleRow 表头序号(下标从0开始)
     * @param wb excel文件
     * @return 读取的数据结果
     * @see
     * @since 1.0
     */
    private static List<List<String>> readToList(int sheetIndex, int skipRows, int titleRow,
                                                 Workbook wb) {
        List<List<String>> ls = new ArrayList<List<String>>();
        Sheet sh = wb.getSheetAt(sheetIndex);
        int rows = sh.getPhysicalNumberOfRows();
        int cells = sh.getRow(titleRow).getPhysicalNumberOfCells();
        Row tRow = sh.getRow(titleRow);
        // 列标题为空的话结束
        for (int c = 0; c < cells; c++ ) {
            if (tRow.getCell(c) == null
                || StringUtils.isEmpty(getStringCellValue(tRow.getCell(c)))) {
                cells = c;
                break;
            }
        }

        for (int i = skipRows; i < rows; i++ ) {
            Row row = sh.getRow(i);
            if (row == null) {
                break;
            }
            List<String> r = new ArrayList<String>(cells);
            boolean dataFlag = true;
            for (int c = 0; c < cells; c++ ) {
                if (row.getCell(c) == null || row.getCell(c).getCellTypeEnum() == CellType.BLANK) {
                    r.add("");
                }
                else {
                    dataFlag = false;
                    r.add(getStringCellValue(row.getCell(c)));
                }
            }
            // 遇到一行全为空结束
            if (dataFlag) {
                break;
            }
            ls.add(r);
        }
        return ls;
    }

    /**
     * <一句话功能简述> <功能详细描述>
     * 
     * @param cell 单元格
     * @return 值
     * @see
     * @since 1.0
     */
    @SuppressWarnings("deprecation")
    private static String getStringCellValue(Cell cell) {
        String value = null;
        switch (cell.getCellTypeEnum()) {
            case NUMERIC: // 数字
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    value = DateUtil.format(cell.getDateCellValue(),
                        DateUtil.DEFAULT_ANOTHER_FORMAT);
                    return value;
                }
                value = point.format(cell.getNumericCellValue());
                break;
            case STRING: // 字符串
                value = cell.getStringCellValue();
                break;
            case FORMULA: // 公式
                value = cell.getStringCellValue();
                break;
            case BLANK: // 空值
                break;
            case BOOLEAN: // Boolean
                break;
            case ERROR: // 故障
                break;
            default:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    value = DateUtil.format(cell.getDateCellValue());
                }
                break;
        }

        return value;
    }

    /**
     * 读取Excel文件，支持2000与2007格式
     * 
     * @param fileName 文件名
     * @param file 文件
     * @param inputStream
     * @return wb
     * @throws Exception yc
     */
    public static Workbook loadWorkbook(String fileName, File file, InputStream inputStream)
        throws Exception {
        if (null == file && StringUtils.isEmpty(fileName)) {
            return null;
        }
        if (inputStream == null) {
            if (null == file) {
                file = new File(fileName);
            }
            if (StringUtils.isEmpty(fileName)) {
                fileName = file.getName();
            }
            inputStream = new FileInputStream(file);
        }

        Workbook wb = null;
        if (fileName.toLowerCase().endsWith(OFFICE_EXCEL_2003_POSTFIX)) {
            try {
                POIFSFileSystem fs = new POIFSFileSystem(inputStream);
                wb = new HSSFWorkbook(fs);
                inputStream.close();
            }
            catch (Exception e) {
                e.printStackTrace();
            }

        }
        else if (fileName.toLowerCase().endsWith(OFFICE_EXCEL_2007_POSTFIX)) {
            try {
                wb = new XSSFWorkbook(inputStream);
                inputStream.close();
            }
            catch (Exception e) {
                e.printStackTrace();
            }
        }
        else {
            throw new Exception("不是一个有效的Excel文件");
        }
        return wb;
    }

    /**
     * 获取文件的sheet名称
     * 
     * @param fileName 文件名
     * @param file 文件
     * @param sheetIndex 工作表索引(下标从0开始)
     * @return sheet名称
     * @throws Exception 文件异常
     */
    public static String getSheetName(String fileName, File file, InputStream inputStream,
                                      int sheetIndex)
        throws Exception {
        Workbook wb = loadWorkbook(fileName, file, inputStream);
        if (null != wb) {
            Sheet sh = wb.getSheetAt(sheetIndex);
            if (sh != null) {
                return sh.getSheetName();
            }
        }
        return "";
    }
}
